package mysql

import "pharymacy-management-system/models"

// InsertStockOperate 添加库存操作
func InsertStockOperate(operateName string) (err error) {
	sqlStr := `insert into w_stock_operate (operate_name) value (?)`
	_, err = db.Exec(sqlStr, operateName)
	return
}

// SelectOperateByName 根据操作名称查询操作
func SelectOperateByName(operateName string) (*models.ParamOperate, error) {
	var count int
	sqlStr := `select count(*) from w_stock_operate where operate_name=?`
	err := db.Get(&count, sqlStr, operateName)
	if err != nil {
		return nil, err
	}
	if count == 0 {
		return nil, nil
	}
	sqlStr = `select operate_id,operate_name from w_stock_operate where operate_name=?`
	var operate models.ParamOperate
	err = db.Get(&operate, sqlStr, operateName)
	if err != nil {
		return nil, err
	}
	return &operate, nil
}

// SelectOperateByID 根据操作ID查询操作
func SelectOperateByID(operateID int) (*models.ParamOperate, error) {
	var count int
	sqlStr := `select count(*) from w_stock_operate where operate_id=?`
	err := db.Get(&count, sqlStr, operateID)
	if err != nil {
		return nil, err
	}
	if count == 0 {
		return nil, nil
	}
	sqlStr = `select operate_id,operate_name from w_stock_operate where operate_id=?`
	var operate models.ParamOperate
	err = db.Get(&operate, sqlStr, operateID)
	if err != nil {
		return nil, err
	}
	return &operate, nil
}

func SelectAllStockOperate() (operateList []models.ParamOperate, err error) {
	sqlStr := `select operate_id,operate_name from w_stock_operate`
	err = db.Select(&operateList, sqlStr)
	return
}

func UpdateStockOperateByID(operate *models.ParamOperate) error {
	sqlStr := `update w_stock_operate set operate_name=? where operate_id=?`
	_, err := db.Exec(sqlStr, operate.OperateName, operate.OperateID)
	return err
}

func CreateStock(stock *models.Stock, history *models.StockHistory) error {
	// 开启事务
	tx, err := db.Beginx()
	if err != nil {
		return err
	}
	// 执行插入语句
	sqlStr1 := `insert into w_stock(stock_id, drug_id, number) value (?,?,?)`
	sqlStr2 := `insert into w_stock_history(stock_id, drug_id, operate_id, number, user_id) value (?,?,?,?,?)`
	tx.MustExec(sqlStr1, stock.StockID, stock.DrugID, stock.Number)
	tx.MustExec(sqlStr2, history.StockID, history.DrugID, history.OperateID, history.Number, history.UserID)
	return tx.Commit()
}

func SelectAllStock() (stockList []models.Stock, err error) {
	sqlStr := `select s.stock_id,s.drug_id,s.number,d.drug_name,d.drug_specs,d.drug_price from w_stock  s join w_drug  d on s.drug_id=d.drug_id`
	rows, err := db.Queryx(sqlStr)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var stock models.Stock
		var drug models.Drug
		err := rows.Scan(&stock.StockID, &stock.DrugID, &stock.Number, &drug.DrugName, &drug.DrugSpecs, &drug.DrugPrice)
		if err != nil {
			return nil, err
		}
		drug.DrugID = stock.DrugID
		stock.Drug = drug
		stockList = append(stockList, stock)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return
}

func UpdateStockAndHistory(stock *models.Stock, history *models.StockHistory) (err error) {
	// 开启事务
	tx, err := db.Beginx()
	if err != nil {
		return err
	}
	sqlStr1 := `update w_stock set number=? where stock_id=?`
	sqlStr2 := `insert into w_stock_history(stock_id, drug_id, operate_id, number, user_id)  value (?,?,?,?,?)`
	tx.MustExec(sqlStr1, stock.Number, stock.StockID)
	tx.MustExec(sqlStr2, history.StockID, history.DrugID, history.OperateID, history.Number, history.UserID)
	return tx.Commit()
}

func GetAllStockHistory() (historyList []models.StockHistory, err error) {
	sqlStr := `select a.stock_id,a.drug_id,a.operate_id,a.number,a.update_time,a.user_id,c.drug_name,d.user_nick_name,e.operate_name from w_stock_history a,w_drug c,w_user d,w_stock_operate e where a.user_id=d.user_id and a.drug_id=c.drug_id and e.operate_id=a.operate_id`
	rows, err := db.Queryx(sqlStr)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var history models.StockHistory
		err = rows.Scan(&history.StockID, &history.DrugID, &history.OperateID, &history.Number, &history.UpdateTime, &history.UserID, &history.DrugName, &history.UserNickName, &history.OperateName)
		if err != nil {
			return nil, err
		}
		historyList = append(historyList, history)
	}
	return
}

func GetStockHistoryByStockId(stockID string) (historyList []models.StockHistory, err error) {
	sqlStr := `select a.stock_id,a.drug_id,a.operate_id,a.number,a.update_time,a.user_id,c.drug_name,d.user_nick_name,e.operate_name from w_stock_history a,w_drug c,w_user d,w_stock_operate e where a.user_id=d.user_id and a.drug_id=c.drug_id and e.operate_id=a.operate_id and a.stock_id=?`
	rows, err := db.Queryx(sqlStr, stockID)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var history models.StockHistory
		err = rows.Scan(&history.StockID, &history.DrugID, &history.OperateID, &history.Number, &history.UpdateTime, &history.UserID, &history.DrugName, &history.UserNickName, &history.OperateName)
		if err != nil {
			return nil, err
		}
		historyList = append(historyList, history)
	}
	return
}
